package com.hotelManager.www.dao;

import com.hotelManager.www.po.SignInRecord;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 签到DAO类
 * 实现签到相关数据库操作
 */
public class SignInDao {
    
    /**
     * 检查用户今日是否已签到
     * @param conn 数据库连接
     * @param userId 用户ID
     * @param date 日期
     * @return 是否已签到
     */
    public boolean isSignedInToday(Connection conn, int userId, Date date) throws SQLException {
        String sql = "SELECT COUNT(*) FROM sign_in_records WHERE user_id = ? AND sign_in_date = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ps.setDate(2, date);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
            return false;
        }
    }

    /**
     * 获取用户连续签到天数
     * @param conn 数据库连接
     * @param userId 用户ID
     * @return 连续签到天数
     */
    public int getConsecutiveSignInDays(Connection conn, int userId) throws SQLException {
        // 获取用户最近的签到记录，按日期降序排序
        String sql = "SELECT sign_in_date FROM sign_in_records WHERE user_id = ? ORDER BY sign_in_date DESC";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ResultSet rs = ps.executeQuery();
            
            if (!rs.next()) {
                return 0; // 没有签到记录
            }
            
            // 获取最近一次签到日期
            Date lastSignInDate = rs.getDate("sign_in_date");
            
            // 计算连续签到天数
            int consecutiveDays = 1;
            Date currentDate = lastSignInDate;
            
            while (rs.next()) {
                Date previousDate = rs.getDate("sign_in_date");
                // 检查是否是连续的天数
                long diffInMillies = currentDate.getTime() - previousDate.getTime();
                long diffInDays = diffInMillies / (24 * 60 * 60 * 1000);
                
                if (diffInDays == 1) {
                    consecutiveDays++;
                    currentDate = previousDate;
                } else {
                    break; // 不连续，退出循环
                }
            }
            
            return consecutiveDays;
        }
    }

    /**
     * 插入签到记录
     * @param conn 数据库连接
     * @param record 签到记录
     * @return 插入是否成功
     */
    public boolean insert(Connection conn, SignInRecord record) throws SQLException {
        String sql = "INSERT INTO sign_in_records (user_id, sign_in_date, points_earned) VALUES (?, ?, ?)";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, record.getUserId());
            ps.setDate(2, record.getSignInDate());
            ps.setInt(3, record.getPointsEarned());
            return ps.executeUpdate() > 0;
        }
    }

    /**
     * 获取用户签到记录
     * @param conn 数据库连接
     * @param userId 用户ID
     * @return 签到记录列表
     */
    public List<SignInRecord> getSignInRecordsByUserId(Connection conn, int userId) throws SQLException {
        String sql = "SELECT * FROM sign_in_records WHERE user_id = ? ORDER BY sign_in_date DESC";
        List<SignInRecord> records = new ArrayList<>();
        
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ResultSet rs = ps.executeQuery();
            
            while (rs.next()) {
                SignInRecord record = new SignInRecord();
                record.setId(rs.getInt("id"));
                record.setUserId(rs.getInt("user_id"));
                record.setSignInDate(rs.getDate("sign_in_date"));
                record.setPointsEarned(rs.getInt("points_earned"));
                records.add(record);
            }
        }
        
        return records;
    }
    
    /**
     * 获取总签到次数
     * @param conn 数据库连接
     * @return 总签到次数
     */
    public int getTotalSignIns(Connection conn) throws SQLException {
        String sql = "SELECT COUNT(*) FROM sign_in_records";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
            return 0;
        }
    }
    
    /**
     * 获取今日签到人数
     * @param conn 数据库连接
     * @return 今日签到人数
     */
    public int getTodaySignIns(Connection conn) throws SQLException {
        String sql = "SELECT COUNT(DISTINCT user_id) FROM sign_in_records WHERE sign_in_date = CURRENT_DATE";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
            return 0;
        }
    }
    
    /**
     * 获取连续签到指定天数以上的用户数量
     * @param conn 数据库连接
     * @param days 连续签到天数
     * @return 用户数量
     */
    public int getConsecutiveSignInsCount(Connection conn, int days) throws SQLException {
        // 这个查询比较复杂，需要先获取所有用户的连续签到天数，然后统计大于等于指定天数的用户数量
        // 由于数据库可能不支持复杂的窗口函数，这里使用Java代码实现
        String sql = "SELECT DISTINCT user_id FROM sign_in_records";
        List<Integer> userIds = new ArrayList<>();
        
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                userIds.add(rs.getInt("user_id"));
            }
        }
        
        int count = 0;
        for (Integer userId : userIds) {
            int consecutiveDays = getConsecutiveSignInDays(conn, userId);
            if (consecutiveDays >= days) {
                count++;
            }
        }
        
        return count;
    }
    
    /**
     * 获取签到最多的前N名用户
     * @param conn 数据库连接
     * @param limit 限制数量
     * @return 用户签到统计列表
     */
    public List<Map<String, Object>> getTopSignInUsers(Connection conn, int limit) throws SQLException {
        String sql = "SELECT u.id, u.username, COUNT(s.id) as sign_in_count " +
                     "FROM users u " +
                     "LEFT JOIN sign_in_records s ON u.id = s.user_id " +
                     "GROUP BY u.id, u.username " +
                     "ORDER BY sign_in_count DESC " +
                     "LIMIT ?";
        
        List<Map<String, Object>> result = new ArrayList<>();
        
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, limit);
            ResultSet rs = ps.executeQuery();
            
            while (rs.next()) {
                Map<String, Object> userStats = new HashMap<>();
                userStats.put("userId", rs.getInt("id"));
                userStats.put("username", rs.getString("username"));
                userStats.put("signInCount", rs.getInt("sign_in_count"));
                result.add(userStats);
            }
        }
        
        return result;
    }
    
    /**
     * 获取最近N天的签到趋势
     * @param conn 数据库连接
     * @param days 天数
     * @return 签到趋势数据
     */
    public List<Map<String, Object>> getSignInTrend(Connection conn, int days) throws SQLException {
        // 生成最近N天的日期列表
        List<Date> dateList = new ArrayList<>();
        long currentTime = System.currentTimeMillis();
        for (int i = days - 1; i >= 0; i--) {
            long dayTime = currentTime - i * 24 * 60 * 60 * 1000L;
            dateList.add(new Date(dayTime));
        }
        
        // 查询每天的签到人数
        List<Map<String, Object>> trend = new ArrayList<>();
        for (Date date : dateList) {
            String sql = "SELECT COUNT(DISTINCT user_id) as count FROM sign_in_records WHERE sign_in_date = ?";
            try (PreparedStatement ps = conn.prepareStatement(sql)) {
                ps.setDate(1, date);
                ResultSet rs = ps.executeQuery();
                
                Map<String, Object> dayStats = new HashMap<>();
                dayStats.put("date", date);
                
                if (rs.next()) {
                    dayStats.put("count", rs.getInt("count"));
                } else {
                    dayStats.put("count", 0);
                }
                
                trend.add(dayStats);
            }
        }
        
        return trend;
    }

    /**
     * 更新用户签到状态
     * @param conn 数据库连接
     * @param userId 用户ID
     * @param status 状态
     * @return 操作是否成功
     */
    public boolean updateUserSignInStatus(Connection conn, int userId, String status) throws SQLException {
        String sql = "UPDATE users SET sign_in_status = ? WHERE id = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, status);
            ps.setInt(2, userId);
            return ps.executeUpdate() > 0;
        }
    }

    /**
     * 获取用户签到状态
     * @param conn 数据库连接
     * @param userId 用户ID
     * @return 签到状态
     */
    public String getUserSignInStatus(Connection conn, int userId) throws SQLException {
        String sql = "SELECT sign_in_status FROM users WHERE id = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getString("sign_in_status");
            }
            return "active"; // 默认状态为活跃
        }
    }
} 